package phylacterium.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;

import phylacterium.beans.StatisticsBean;
import phylacterium.entities.LoginEntity;
import phylacterium.entities.PoiEntity;
import phylacterium.entities.TrackEntity;
import phylacterium.entities.UserEntity;

public class DBOperations {

	private static Connection con = null;

	private static void connect() throws DBException, SQLException,
			ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");

		con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/phylacterium", "phylacterium",
				"phylacterium");
	}

	public static ArrayList<String[]> select(String sql) throws DBException {
		try {
			connect();
			Statement stmt;

			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();

			int numCols = rsmd.getColumnCount();

			ArrayList<String[]> wynik = new ArrayList<String[]>();

			while (rs.next()) {
				String tmp[] = new String[numCols];
				for (int i = 1; i <= numCols; i++) {
					if (rsmd.getColumnType(i) == Types.FLOAT) {
						float val = rs.getFloat(i);
						if (!rs.wasNull())
							tmp[i - 1] = Float.toString(val);
					} else if (rsmd.getColumnType(i) == Types.VARCHAR) {
						tmp[i - 1] = rs.getString(i);
					} else if (rsmd.getColumnType(i) == Types.INTEGER
							|| rsmd.getColumnType(i) == Types.BIGINT) {
						int val = rs.getInt(i);
						if (!rs.wasNull())
							tmp[i - 1] = new Integer(val).toString();
					} else if (rsmd.getColumnType(i) == Types.CHAR) {
						String val = rs.getString(i);
						if (!rs.wasNull())
							tmp[i - 1] = new Character(val.charAt(0))
									.toString();
					}

				}
				wynik.add(tmp);
			}
			rs.close();
			stmt.close();

			return wynik;
		} catch (SQLException e) {
			sqlCatch(e);
			return null;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		} finally {
			disconnect();
		}

	}

	public static boolean createUser(UserEntity newUser) throws DBException {
		try {
			connect();

			String sql = new String(
					"INSERT INTO Users(login, pass_hash, name, age, gender, education, favourite_genre) VALUES (?,?,?,?,?,?,?)");

			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1, newUser.login);
			stmt.setString(2,
					new Integer(newUser.password.hashCode()).toString());
			if (newUser.name == null)
				stmt.setNull(3, Types.VARCHAR);
			else
				stmt.setString(3, newUser.name);

			if (newUser.age == null)
				stmt.setNull(4, Types.INTEGER);
			else
				stmt.setInt(4, newUser.age);

			if (newUser.gender == null)
				stmt.setNull(5, Types.CHAR);
			else
				stmt.setString(5, newUser.gender.toString());

			if (newUser.education == null)
				stmt.setNull(6, Types.INTEGER);
			else
				stmt.setInt(6, newUser.education);

			if (newUser.favouriteGenre == null)
				stmt.setNull(7, Types.INTEGER);
			else
				stmt.setInt(7, newUser.favouriteGenre);

			int changes = stmt.executeUpdate();
			stmt.close();
			if (changes == 0)
				return false;
			else {
				LoginEntity login = new LoginEntity();
				login.login = newUser.login;
				login.password = newUser.password;
				checkLogin(login);
				newUser.id = login.id;
				return true;
			}
		} catch (SQLException e) {
			sqlCatch(e);
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		} finally {
			disconnect();
		}

	}

	public static boolean checkLogin(LoginEntity login) throws DBException {
		try {
			connect();

			String sql = new String(
					"SELECT login, pass_hash, name, id FROM Users where login=? AND pass_hash=?");

			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1, login.login);
			stmt.setString(2, new Integer(login.password.hashCode()).toString());

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				login.name = rs.getString(3);
				if (login.name == null)
					login.name = login.login;
				login.id = rs.getInt(4);
			}

			rs.close();
			stmt.close();

			if (login.name != null)
				return true;
			else
				return false;

		} catch (SQLException e) {
			sqlCatch(e);
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		} finally {
			disconnect();
		}

	}

	public static UserEntity getAccountInfo(int id) throws DBException {
		UserEntity user = new UserEntity();

		ArrayList<String[]> userQuery = select("SELECT login, name, age, gender, education, favourite_genre FROM Users where id=\""
				+ id + "\"");
		if (userQuery.size() == 0)
			throw new DBException(10, "User of supplied id does not exist");
		String[] userInfo = userQuery.get(0);

		user.login = userInfo[0];
		user.name = userInfo[1];
		if (userInfo[2] != null) {
			user.age = Integer.parseInt(userInfo[2]);
			user.ageField = userInfo[2];
		}
		if (userInfo[3] != null) {
			user.gender = userInfo[3].charAt(0);
		}
		if (userInfo[4] != null) {
			user.education = Integer.parseInt(userInfo[4]);
		}
		if (userInfo[5] != null) {
			user.favouriteGenre = Integer.parseInt(userInfo[5]);
		}

		return user;
	}

	public static void updateUser(UserEntity updatedUser) throws DBException {
		try {
			connect();

			String sql;

			if (updatedUser.newPassword != null) {
				sql = new String(
						"UPDATE Users SET name=?, age=?, gender=?, education=?, favourite_genre=?, pass_hash=? WHERE id=?");
			} else
				sql = new String(
						"UPDATE Users SET name=?, age=?, gender=?, education=?, favourite_genre=? WHERE id=?");

			PreparedStatement stmt = con.prepareStatement(sql);

			if (updatedUser.name == null)
				stmt.setNull(1, Types.VARCHAR);
			else
				stmt.setString(1, updatedUser.name);

			if (updatedUser.age == null)
				stmt.setNull(2, Types.INTEGER);
			else
				stmt.setInt(2, updatedUser.age);

			if (updatedUser.gender == null)
				stmt.setNull(3, Types.CHAR);
			else
				stmt.setString(3, updatedUser.gender.toString());

			if (updatedUser.education == null)
				stmt.setNull(4, Types.INTEGER);
			else
				stmt.setInt(4, updatedUser.education);

			if (updatedUser.favouriteGenre == null)
				stmt.setNull(5, Types.INTEGER);
			else
				stmt.setInt(5, updatedUser.favouriteGenre);

			if (updatedUser.newPassword != null) {
				stmt.setString(6,
						new Integer(updatedUser.newPassword.hashCode())
								.toString());
				stmt.setInt(7, updatedUser.id);
			} else {
				stmt.setInt(6, updatedUser.id);
			}

			int changes = stmt.executeUpdate();
			stmt.close();

			if (changes == 0) {
				throw new DBException(10, "User of supplied id does not exist");
			}

		} catch (SQLException e) {
			sqlCatch(e);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			disconnect();
		}
	}

	private static ArrayList<PoiEntity> convertResultToStatistics(
			ArrayList<String[]> results) {
		String lastGroup = null;
		PoiEntity poi = null;

		ArrayList<PoiEntity> pois = new ArrayList<PoiEntity>();

		for (String[] row : results) {
			if (lastGroup == null) {
				lastGroup = row[0];
				poi = new PoiEntity();
				poi.poiName = lastGroup;
			} else if (!row[0].equalsIgnoreCase(lastGroup)) {
				pois.add(poi);

				lastGroup = row[0];
				poi = new PoiEntity();
				poi.poiName = lastGroup;
			}
			// Było coś słuchane
			if (row[1] != null) {
				poi.tracks.add(new TrackEntity(row[1], row[2], Integer
						.parseInt(row[3])));
			}
			
		}

		if (poi != null) {
			pois.add(poi);
		}

		return pois;
	}

	public static void getGlobalStatistics(StatisticsBean stats, int id)
			throws DBException {
		String sql;
		if (id > 0)
			sql = new String(
					"SELECT "+
					"poi, artist, title, play_count "+ 
					"FROM "+ 
					"StatisticsGlobal "+
					"WHERE poi_user = 0 OR poi_user = \""+id+"\"");
		else
			sql = new String(
					"SELECT "+
					"poi, artist, title, play_count "+ 
					"FROM "+ 
					"StatisticsGlobal "+
					"WHERE poi_user = 0");
		ArrayList<String[]> results = select(sql);
		stats.globalPois = convertResultToStatistics(results);

	}

	public static void getUserStatistics(StatisticsBean stats, int id)
			throws DBException {
		String sql = new String(
				"SELECT "+
				"p.name, a.name, t.title, COUNT(l.user), p.user "+ 
				"FROM "+ 
				"POIs p LEFT OUTER JOIN POITracks pt ON p.id=pt.poi LEFT OUTER JOIN (SELECT * FROM ListenedTracks WHERE user=\""+ id +"\") l ON l.id=pt.listened_track LEFT OUTER JOIN Tracks t ON l.track=t.id LEFT OUTER JOIN Artists a ON t.artist=a.id "+
				"WHERE p.user = 0 OR p.user=\""+id+"\" "+
				"GROUP BY "+ 
				"p.id, t.id "+
				"ORDER BY "+
				"1 ASC,4 DESC");
		ArrayList<String[]> results = select(sql);
		stats.ownPois = convertResultToStatistics(results);
	}

	// public static ArrayList<String[]> select(String polecenie, int id)
	// throws DBException {
	// try {
	// PreparedStatement stmt = con.prepareStatement(polecenie);
	// stmt.setInt(1, id);
	// ResultSet rs = stmt.executeQuery();
	// ResultSetMetaData rsmd = rs.getMetaData();
	//
	// int numCols = rsmd.getColumnCount();
	//
	// ArrayList<String[]> wynik = new ArrayList<String[]>();
	//
	// while (rs.next()) {
	// String tmp[] = new String[numCols];
	// for (int i = 1; i <= numCols; i++) {
	// if (rsmd.getColumnType(i) == Types.NUMERIC) {
	// tmp[i - 1] = Float.toString(rs.getFloat(i));
	// if (rs.wasNull()) {
	// tmp[i - 1] = "";
	// } else {
	// if (tmp[i - 1].endsWith(".0"))
	// tmp[i - 1] = Integer.toString(rs.getInt(i));
	//
	// }
	//
	// } else if (rsmd.getColumnType(i) == Types.VARCHAR)
	// tmp[i - 1] = rs.getString(i);
	// }
	// wynik.add(tmp);
	// }
	// rs.close();
	// stmt.close();
	// return wynik;
	// } catch (SQLException e) {
	// sqlCatch(e);
	// return null;
	// } catch (Exception e) {
	// e.printStackTrace();
	// return null;
	// }
	//
	// }
	//
	// public static ArrayList<String[]> select(String polecenie,
	// ArrayList<Integer> id) throws DBException {
	// try {
	// if (id.size() == 0)
	// return new ArrayList<String[]>();
	//
	// String zmienne = new String();
	//
	// zmienne += "?";
	//
	// for (int i = 1; i < id.size(); i++)
	// zmienne += ",?";
	//
	// polecenie = polecenie.replace("#", zmienne);
	//
	// PreparedStatement stmt = con.prepareStatement(polecenie);
	// for (int i = 0; i < id.size(); i++)
	// stmt.setInt(i + 1, id.get(i));
	// ResultSet rs = stmt.executeQuery();
	// ResultSetMetaData rsmd = rs.getMetaData();
	//
	// int numCols = rsmd.getColumnCount();
	//
	// ArrayList<String[]> wynik = new ArrayList<String[]>();
	//
	// while (rs.next()) {
	// String tmp[] = new String[numCols];
	// for (int i = 1; i <= numCols; i++) {
	// if (rsmd.getColumnType(i) == Types.NUMERIC) {
	// tmp[i - 1] = Float.toString(rs.getFloat(i));
	// if (rs.wasNull()) {
	// tmp[i - 1] = "";
	// } else {
	// if (tmp[i - 1].endsWith(".0"))
	// tmp[i - 1] = Integer.toString(rs.getInt(i));
	//
	// }
	//
	// } else if (rsmd.getColumnType(i) == Types.VARCHAR)
	// tmp[i - 1] = rs.getString(i);
	// }
	// wynik.add(tmp);
	// }
	// rs.close();
	// stmt.close();
	// return wynik;
	// } catch (SQLException e) {
	// sqlCatch(e);
	// return null;
	// } catch (Exception e) {
	// e.printStackTrace();
	// return null;
	// }
	//
	// }
	//
	// public static ArrayList<String[]> select(String polecenie, String text)
	// throws DBException {
	// try {
	// PreparedStatement stmt = con.prepareStatement(polecenie);
	// stmt.setString(1, text);
	// ResultSet rs = stmt.executeQuery();
	// ResultSetMetaData rsmd = rs.getMetaData();
	//
	// int numCols = rsmd.getColumnCount();
	//
	// ArrayList<String[]> wynik = new ArrayList<String[]>();
	//
	// while (rs.next()) {
	// String tmp[] = new String[numCols];
	// for (int i = 1; i <= numCols; i++) {
	// if (rsmd.getColumnType(i) == Types.NUMERIC) {
	// tmp[i - 1] = Float.toString(rs.getFloat(i));
	// if (rs.wasNull()) {
	// tmp[i - 1] = "";
	// } else {
	// if (tmp[i - 1].endsWith(".0"))
	// tmp[i - 1] = Integer.toString(rs.getInt(i));
	//
	// }
	//
	// } else if (rsmd.getColumnType(i) == Types.VARCHAR)
	// tmp[i - 1] = rs.getString(i);
	// }
	// wynik.add(tmp);
	// }
	// rs.close();
	// stmt.close();
	// return wynik;
	// } catch (SQLException e) {
	// sqlCatch(e);
	// return null;
	// } catch (Exception e) {
	// e.printStackTrace();
	// return null;
	// }
	//
	// }

	public static boolean insertToStore(int user, int id) throws DBException {
		try {
			String polecenie = new String(
					"INSERT INTO poczekalnia(uzytkownik,produkt) VALUES (?,?)");

			PreparedStatement stmt = con.prepareStatement(polecenie);
			stmt.setInt(1, user);
			stmt.setInt(2, id);

			int changes = stmt.executeUpdate();
			stmt.close();
			if (changes == 0)
				return false;
			else
				return true;
		} catch (SQLException e) {
			sqlCatch(e);
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

	}

	public static boolean removeFromStore(int user, int id) throws DBException {
		try {
			String polecenie = new String(
					"DELETE FROM poczekalnia WHERE uzytkownik=? AND produkt=?");

			PreparedStatement stmt = con.prepareStatement(polecenie);
			stmt.setInt(1, user);
			stmt.setInt(2, id);

			int changes = stmt.executeUpdate();
			stmt.close();
			if (changes == 0)
				return false;
			else
				return true;
		} catch (SQLException e) {
			sqlCatch(e);
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

	}

	public static boolean checkStore(int user, int id) throws DBException {
		try {
			String polecenie = "SELECT * FROM poczekalnia WHERE uzytkownik=? AND produkt=?";

			PreparedStatement stmt = con.prepareStatement(polecenie);
			stmt.setInt(1, user);
			stmt.setInt(2, id);
			ResultSet rs = stmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();

			boolean next = rs.next();
			rs.close();
			stmt.close();

			return next;
		} catch (SQLException e) {
			sqlCatch(e);
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

	}

	private static void disconnect() throws DBException {
		try {
			if (con != null && !con.isClosed())
				con.close();
		} catch (SQLException e) {
			sqlCatch(e);
		}
	}

	private static void sqlCatch(SQLException exception) throws DBException {
		exception.printStackTrace();
		String message = new String();
		if (exception.getErrorCode() == 0)
			message = "Database is probably down";
		else if (exception.getErrorCode() == 2292)
			message = "Cannot remove element used somewhere else";
		else if (exception.getErrorCode() == 1)
			message = "That element already exists";
		else if (exception.getErrorCode() == 942)
			message = "Relation or perspective does not exist";
		else if (exception.getErrorCode() == 923)
			message = "Keyword FROM missing";
		else if (exception.getErrorCode() == 904)
			message = "Wrong atribute name";
		else if (exception.getErrorCode() == 905)
			message = "Keyword missing";
		else if (exception.getErrorCode() == 936)
			message = "Expression missing";
		else if (exception.getErrorCode() == 937)
			message = "Wrong use of group function";
		else if (exception.getErrorCode() == 900)
			message = "Wrong SQL expression";
		else if (exception.getErrorCode() == 1438)
			message = "Too large field value";
		else if (exception.getErrorCode() == 2290)
			message = "Entered data is not valid";
		else if (exception.getErrorCode() == 911)
			message = "Wrong character";
		else if (exception.getErrorCode() == 3115)
			message = "Data type or represantation not supported";
		else if (exception.getErrorCode() == 1821)
			message = "Unresolved date format";
		else if (exception.getErrorCode() == 918)
			message = "Ambigous column definition";
		else if (exception.getErrorCode() == 17104)
			message = "SQL instruction can not be blank";
		else if (exception.getErrorCode() == 907)
			message = "Right paranthesis missing";
		else if (exception.getErrorCode() == 1400)
			message = "Required field missing";
		else if (exception.getErrorCode() == 1407)
			message = "Can not set NULL to required field";
		else if (exception.getErrorCode() == 1858)
			message = "Not numeric parameter in numeric field";
		else if (exception.getErrorCode() == 1843)
			message = "Wrong month";
		else if (exception.getErrorCode() == 1847)
			message = "Wrong day number";
		else if (exception.getErrorCode() == 25156)
			message = "Outer join not supported";
		else if (exception.getErrorCode() == 12505)
			message = "Database not accessible (propably down)";
		else if (exception.getErrorCode() == 1062)
			message = "Unique index violated";
		else
			message = "error " + exception.getErrorCode() + ": "
					+ exception.getMessage();

		DBException e = new DBException(exception.getErrorCode(), message);
		e.sqlException = exception;
		throw e;

	}

}
